package swd.work.dataview_wizard_detail;

import com.pub.database.*;
import java.util.*;

public class Business {
   QueryBean gqbnDAO=null;
   public Business(QueryBean pqbnDAO){
      gqbnDAO=pqbnDAO;
   }
   String selectTableDataToFld(View view) throws Exception {
      String lstrSQL=null;
      Hashtable<String,Object> lhtTableData=null;
      try{
         lstrSQL="select fields_list,tables_list,sql_condition,orderby_fields,orderby_last_field_seqno,groupby_fields,having_condition,db_alias from wkf_dataview_master where prg_no='"+view.getprg_no()+"' and dataview_seqno='"+view.getdataview_seqno()+"'";
         lhtTableData=gqbnDAO.readSingleRowData(lstrSQL);
         if(lhtTableData==null){
            return "查無資料";
         }
         view.setfields_list(String.valueOf((lhtTableData.get("FIELDS_LIST").toString())));
         view.settables_list(String.valueOf((lhtTableData.get("TABLES_LIST").toString())));
         view.setsql_condition(String.valueOf((lhtTableData.get("SQL_CONDITION").toString())));
         view.setorderby_fields(String.valueOf((lhtTableData.get("ORDERBY_FIELDS").toString())));
         view.setorderby_last_field_seqno(String.valueOf((lhtTableData.get("ORDERBY_LAST_FIELD_SEQNO").toString())));
         view.setgroupby_fields(String.valueOf((lhtTableData.get("GROUPBY_FIELDS").toString())));
         view.sethaving_condition(String.valueOf((lhtTableData.get("HAVING_CONDITION").toString())));
         view.setdb_from(String.valueOf((lhtTableData.get("DB_ALIAS").toString())));
         return "";
      }
      catch(Exception e){
         throw new Exception(e.toString());
      }
      finally{
         lhtTableData.clear();
         lhtTableData=null;
      }
   }
   String combineSQLStatement(View view) throws Exception{
     String lstrSQL=null;
     try{
         //將從wkf_dataview_master select出來的資料組成SQL語句
         lstrSQL="select "+view.getfields_list()+" from "+view.gettables_list();
         if(view.getsql_condition()!=null && view.getsql_condition().length()!=0){
             lstrSQL+=" where rownum=1 and "+view.getsql_condition();         }
         else{lstrSQL+=" where rownum=1";}
         if(view.getorderby_fields()!=null && view.getorderby_fields().length()!=0){
             lstrSQL+=" order by "+view.getorderby_fields();
             if(view.getorderby_last_field_seqno()!=null && view.getorderby_last_field_seqno().length()!=0){
                 lstrSQL+=" "+view.getorderby_last_field_seqno(); } }
         if(view.getgroupby_fields()!=null && view.getgroupby_fields().length()!=0){
             lstrSQL+=" group by "+view.getgroupby_fields();      }
         return lstrSQL;
           }
     catch(Exception e){
           throw new Exception(e.toString());
       }


   }
   String getColumnsList(View view) throws Exception{
       String lstrSQL=null;
       String lstrOption=null;
       Hashtable<String,Object> lhtTableData=null;
       ArrayList<String> AL=null;
       int i=0;
       QueryBean gb=null;
       try{
           gb=new QueryBean(view.getdb_from(),view.getDBDebug(),view.getSourceCharset(),view.getTargetCharset());
           lstrSQL=view.getsql_statement();
           lhtTableData=gb.readSingleRowData("select nvl(count(*),0) as cnt from ("+lstrSQL+")");
           /*
           Query 2次是為了check SQL語法是否有錯誤
           若SQL語法錯誤或Table不存在該DB的話,第1次的Query,Result會回傳null
           */
           if(lhtTableData!=null){
               lhtTableData=gb.readSingleRowData(lstrSQL);
               lhtTableData = null; //將Result Set清空,因為不會用到
               AL = gb.getArlcolumnLabel();
               //System.out.println(AL.size());
               for (i = 0; i < AL.size(); i++) {
                   lstrOption += "<option value='" + AL.get(i).toString() +
                           "'>" + AL.get(i).toString() + "</option>";
                   //System.out.println("aaaaa===>"+AL.get(i).toString());
               }
               view.setcols_option(lstrOption);
               AL = null;
               return "";
           }
           else{
               return "語法錯誤或Table不存在"+view.getdb_from().toString();
           }

       }
       catch(Exception e){
            throw new Exception(e.toString());
       }
       finally{
           try{
        	   
        	   gb.close();
               gb = null;
               
           }
           catch(Exception e){
               System.out.println("can not close querybean in getColumnsList Method");
           }
       }

   }
   String exeUpdSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add(view.getprg_no()==null ? null :String.valueOf(view.getprg_no()));
         lvtrParam.add(view.gettable_id()==null ? null :String.valueOf(view.gettable_id()));
         lvtrParam.add(view.gettr_align()==null ? null :String.valueOf(view.gettr_align()));
         lvtrParam.add(view.gettr_class()==null ? null :String.valueOf(view.gettr_class()));
         lvtrParam.add(view.gettr_width()==null ? null :String.valueOf(view.gettr_width()));
         lvtrParam.add(view.gettr_height()==null ? null :String.valueOf(view.gettr_height()));
         lvtrParam.add(view.gettable_name()==null ? null :String.valueOf(view.gettable_name()));
         lvtrParam.add(view.getfields_list()==null ? null :String.valueOf(view.getfields_list()));
         lvtrParam.add(view.getfields_type()==null ? null :String.valueOf(view.getfields_type()));
         lvtrParam.add(view.getform_action()==null ? null :String.valueOf(view.getform_action()));
         lvtrParam.add(view.getform_method()==null ? null :String.valueOf(view.getform_method()));
         lvtrParam.add(view.gettable_align()==null ? null :String.valueOf(view.gettable_align()));
         lvtrParam.add(view.gettable_width()==null ? null :String.valueOf(view.gettable_width()));
         lvtrParam.add(view.gettable_border()==null ? null :String.valueOf(view.gettable_border()));
         lvtrParam.add(view.gettable_height()==null ? null :String.valueOf(view.gettable_height()));
         lvtrParam.add(view.gettable_cellpadding()==null ? null :String.valueOf(view.gettable_cellpadding()));
         lvtrParam.add(view.gettable_cellspacing()==null ? null :String.valueOf(view.gettable_cellspacing()));
         lstrSQL="update dataview_setup set prg_no=?,table_id=?,tr_align=?,tr_class=?,tr_width=?,tr_height=?,table_name=?,fields_list=?,fields_type=?,form_action=?,form_method=?,table_align=?,table_width=?,table_border=?,table_height=?,table_cellpadding=?,table_cellspacing=? where prg_no='" + view.getprg_no() + "'";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){
            return "dataview_setup";
         }
         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e.toString());
      }
      finally{
         lvtrParam.clear();
      }
   }
   String exeInsSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add((view.getprg_no()==null ? null : view.getprg_no()));
         lvtrParam.add((view.getform_method()==null ? null : view.getform_method()));
         lvtrParam.add((view.getform_action()==null ? null : view.getform_action()));
         lvtrParam.add((view.gettable_name()==null ? null : view.gettable_name()));
         lvtrParam.add((view.gettable_id()==null ? null : view.gettable_id()));
         lvtrParam.add((view.gettable_width()==null ? null : view.gettable_width()));
         lvtrParam.add((view.gettable_height()==null ? null : view.gettable_height()));
         lvtrParam.add((view.gettable_align()==null ? null : view.gettable_align()));
         lvtrParam.add((view.gettable_border()==null ? null : view.gettable_border()));
         lvtrParam.add((view.gettable_cellpadding()==null ? null : view.gettable_cellpadding()));
         lvtrParam.add((view.gettable_cellspacing()==null ? null : view.gettable_cellspacing()));
         lvtrParam.add((view.gettr_width()==null ? null : view.gettr_width()));
         lvtrParam.add((view.gettr_height()==null ? null : view.gettr_height()));
         lvtrParam.add((view.gettr_align()==null ? null : view.gettr_align()));
         lvtrParam.add((view.gettr_class()==null ? null : view.gettr_class()));
         lvtrParam.add((view.getfields_list()==null ? null : view.getfields_list()));
         lvtrParam.add((view.getfields_type()==null ? null : view.getfields_type()));
         lstrSQL="insert into dataview_setup(prg_no,form_method,form_action,table_name,table_id,table_width,table_height,table_align,table_border,table_cellpadding,table_cellspacing,tr_width,tr_height,tr_align,tr_class,fields_list,fields_type) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){;
            return "dataview_setup";
         }
         lvtrParam.clear();

         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e);
      }
      finally{
         lvtrParam.clear();
      }
   }
   String checkNotNullFld(View view) throws Exception{
      StringBuffer lstbNullFldList=new StringBuffer();
      return (lstbNullFldList.toString().equals("") ? "" : lstbNullFldList.delete(lstbNullFldList.length()-1,lstbNullFldList.length()).toString());
   }
   void setInitValueForAdd(View view,String pstrUserID) throws Exception{
   }
   void setReadonlyForAdd(View view) throws Exception{
   }
}
